package com.ds.app.database;

import com.ds.app.AppNotFoundException;
import com.ds.common.CommonConfig;
import com.ds.common.logging.Log;
import com.ds.common.logging.LogFactory;
import com.ds.org.conf.OrgConfig;
import com.ds.org.conf.OrgConstants;
import com.ds.org.conf.Query;
import com.ds.org.conf.Query.ColumnMapping;
import com.ds.org.conf.Query.SqlClause;
import com.ds.server.eumus.ConfigCode;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

public class DBAppDataBaseManager extends DBBaseManager {
    protected static Log log = LogFactory.getLog(OrgConstants.CONFIG_KEY.getType(), DBAppDataBaseManager.class);
    private static Map<ConfigCode, DBAppDataBaseManager> managers = new HashMap<ConfigCode, DBAppDataBaseManager>();
    private ConfigCode systemId;

    private OrgConfig getConfig() {

        OrgConfig config = OrgConfig.getInstance(this.systemId);
        return config;
    }

    private OrgConfig config;

    private boolean cacheEnabled;

    public static DBAppDataBaseManager getInstance(ConfigCode systemId) {
        DBAppDataBaseManager appManager = managers.get(systemId);

        if (appManager == null) {
            appManager = new DBAppDataBaseManager(systemId);
            managers.put(systemId, appManager);
        }

        return appManager;
    }

    public DbApp creatApp() {
        DbApp app = new DbApp(this.systemId);
        return app;
    }

    public synchronized DbApp loadAppByKey(String appId) throws AppNotFoundException {
        DbApp app = null;
        try {
            app = loadBasic(appId);

        } catch (Exception e) {
            throw new AppNotFoundException("app '" + appId + "' not found!");
        }
        return app;
    }

    DBAppDataBaseManager(ConfigCode systemId) {
        super(systemId);
        this.systemId = systemId;
        String enabled = CommonConfig.getValue(OrgConstants.CONFIG_KEY.getType() + ".cache.enabled");
        this.cacheEnabled = Boolean.valueOf(enabled).booleanValue();
        this.config = OrgConfig.getInstance();
    }

    public DbApp decodeRow(ResultSet rs, Boolean all) {
        SqlClause sqlClause = getConfig().getQuery("App").getSqlClause("basic");
        Map columnMap = sqlClause.getColumnMappings();

        DbApp app = new DbApp(systemId);
        String ID = getString(rs, columnMap.get("appId"));
        app.setID(ID);
        if (all) {
            String name = getString(rs, columnMap.get("appName"));
            String enName = getString(rs, columnMap.get("enName"));
            String icon = getString(rs, columnMap.get("icon"));
            Integer index = getInt(rs, columnMap.get("index"));
            String parentId = getString(rs, columnMap.get("parentAppId"));
            if (columnMap.containsKey("appBrief")) {
                String appBrief = getString(rs, columnMap.get("appBrief"));
                app.setBrief(appBrief);
            }
            app.setName(name);
            app.setEnName(enName);
            app.setIcon(icon);
            app.setParentId(parentId);
            app.setIndex(index);
        }
        return app;

    }

    /**
     * 载入应用基本信息
     */
    private DbApp loadBasic(String ID) throws AppNotFoundException {
        ResultSet rs = null;
        Query query = null;
        SqlClause sqlClause = null;
        Map columnMap = null;
        String strSql = null;
        Connection c = null;
        PreparedStatement ps = null;
        DbApp app = null;
        // 1.基本信息
        query = getConfig().getQuery("App");
        if (query == null) {
            log.error("The app-query-sql is null, please specify it!");
            throw new AppNotFoundException();
        }
        sqlClause = query.getSqlClause("basic");
        if (sqlClause == null) {
            log.error("The app-query-sql is null, please specify it!");
            throw new AppNotFoundException();
        }
        columnMap = sqlClause.getColumnMappings();
        strSql = buildSql(sqlClause, ID);

        try {
            c = this.getConnection();
            ps = c.prepareStatement(strSql.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            if (ps.execute()) {
                rs = ps.executeQuery();
                if (!rs.next()) {
                    throw new AppNotFoundException("App " + ID + " not found.");
                }
                app = this.decodeRow(rs, true);

            } else {
                throw new AppNotFoundException("App " + ID + " not found.");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            getManager().close(ps, rs);
            freeConnection(c);
        }
        return app;
    }

    /**
     * 载入应用信息
     */
    void loadChildren(DbApp app) {
        ResultSet rs = null;
        Query query = null;
        SqlClause sqlClause = null;
        Map columnMap = null;
        String strSql = null;
        Connection c = null;
        PreparedStatement ps = null;
        // 5.子应用信息
        query = getConfig().getQuery("App");
        if (query != null) {
            sqlClause = query.getSqlClause("basic");
            if (sqlClause != null) {
                columnMap = sqlClause.getColumnMappings();
                strSql = buildGetChildrenSql(sqlClause, app.getID());

                app.setChildIdList_is_initialized(true);
                app.setChildIdList(null);

                try {
                    c = this.getConnection();
                    ps = c.prepareStatement(strSql.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                    if (ps.execute()) {
                        rs = ps.executeQuery();
                        while (rs.next()) {
                            app.addChild(getString(rs, columnMap.get("appId")));
                        }
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                } finally {
                    getManager().close(ps, rs);
                    freeConnection(c);
                }
            }
        }
    }

    /**
     * 对人员新增授权
     *
     * @param app
     * @throws Exception
     */
    synchronized void loadPersons(DbApp app) {
        ResultSet rs = null;
        Query query = null;
        SqlClause sqlClause = null;
        Map columnMap = null;
        String strSql = null;
        Connection c = null;
        PreparedStatement ps = null;

        // 3.人员授权信息
        query = getConfig().getQuery("App-Person");
        if (query != null) {
            sqlClause = query.getSqlClause("basic");
            if (sqlClause != null) {
                columnMap = sqlClause.getColumnMappings();
                strSql = buildSql(sqlClause, app.getID());

                app.setPersonIdList_is_initialized(true);
                app.setPersonIdList(null);

                try {
                    c = this.getConnection();
                    ps = c.prepareStatement(strSql.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                    if (ps.execute()) {
                        rs = ps.executeQuery();
                        while (rs.next()) {
                            app.addPerson(getString(rs, columnMap.get("personId")));
                        }
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                } finally {
                    getManager().close(ps, rs);
                    freeConnection(c);
                }
            }
        }
    }

    /**
     * 对组织机构授权
     *
     * @param app
     * @throws Exception
     */
    synchronized void loadOrgs(DbApp app) {
        ResultSet rs = null;
        Query query = null;
        SqlClause sqlClause = null;
        Map columnMap = null;
        String strSql = null;
        Connection c = null;
        PreparedStatement ps = null;
        // 3.人员授权信息
        query = getConfig().getQuery("App-Org");
        if (query != null) {
            sqlClause = query.getSqlClause("basic");
            if (sqlClause != null) {
                columnMap = sqlClause.getColumnMappings();
                strSql = buildSql(sqlClause, app.getID());

                app.setOrgIdList_is_initialized(true);
                app.setOrgIdList(null);

                try {
                    c = this.getConnection();
                    ps = c.prepareStatement(strSql.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                    if (ps.execute()) {
                        rs = ps.executeQuery();
                        while (rs.next()) {
                            app.addOrg(getString(rs, columnMap.get("orgId")));
                        }
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                } finally {
                    getManager().close(ps, rs);
                    freeConnection(c);
                }
            }
        }
    }

    /**
     * 对权限点禁用
     *
     * @param app
     * @throws Exception
     */
    synchronized void loadDisablePersons(DbApp app) {
        ResultSet rs = null;
        Query query = null;
        SqlClause sqlClause = null;
        Map columnMap = null;
        String strSql = null;
        Connection c = null;
        PreparedStatement ps = null;

        // 3.人员授权信息
        query = getConfig().getQuery("App-DisablePerson");
        if (query != null) {
            sqlClause = query.getSqlClause("basic");
            if (sqlClause != null) {
                columnMap = sqlClause.getColumnMappings();
                strSql = buildSql(sqlClause, app.getID());

                try {
                    c = this.getConnection();
                    ps = c.prepareStatement(strSql.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                    if (ps.execute()) {
                        rs = ps.executeQuery();
                        while (rs.next()) {
                            app.addDisablePerson(getString(rs, columnMap.get("personId")));
                        }
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                } finally {
                    getManager().close(ps, rs);
                    freeConnection(c);
                }
            }
        }
    }

    synchronized void loadRoles(DbApp app) {
        ResultSet rs = null;
        Query query = null;
        SqlClause sqlClause = null;
        Map columnMap = null;
        String strSql = null;
        Connection c = null;
        PreparedStatement ps = null;

        // 3.角色信息
        query = getConfig().getQuery("App-PersonRole");
        if (query != null) {
            sqlClause = query.getSqlClause("basic");
            if (sqlClause != null) {
                columnMap = sqlClause.getColumnMappings();
                strSql = buildSql(sqlClause, app.getID());
                app.setRoleIdList_is_initialized(true);
                app.setRecursiveChildIdList(null);

                try {
                    c = this.getConnection();
                    ps = c.prepareStatement(strSql.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                    if (ps.execute()) {
                        rs = ps.executeQuery();
                        while (rs.next()) {
                            app.addRole(getString(rs, columnMap.get("personRoleId")));
                        }
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                } finally {
                    getManager().close(ps, rs);
                    freeConnection(c);
                }

            }
        }
    }

    synchronized void loadDuties(DbApp app) {

        ResultSet rs = null;
        Query query = null;
        SqlClause sqlClause = null;
        Map columnMap = null;
        String strSql = null;
        Connection c = null;
        PreparedStatement ps = null;

        // 3.职务信息
        query = getConfig().getQuery("App-PersonDuty");
        if (query != null) {
            sqlClause = query.getSqlClause("basic");
            if (sqlClause != null) {
                columnMap = sqlClause.getColumnMappings();
                strSql = buildSql(sqlClause, app.getID());
                app.setRoleIdList_is_initialized(true);
                app.setRecursiveChildIdList(null);

                try {
                    c = this.getConnection();
                    ps = c.prepareStatement(strSql.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                    if (ps.execute()) {
                        rs = ps.executeQuery();
                        while (rs.next()) {
                            app.addRole(getString(rs, columnMap.get("personDutyId")));
                        }
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                } finally {
                    getManager().close(ps, rs);
                    freeConnection(c);
                }

            }
        }

    }

    synchronized void loadPositions(DbApp app) {
        ResultSet rs = null;
        Query query = null;
        SqlClause sqlClause = null;
        Map columnMap = null;
        String strSql = null;
        Connection c = null;
        PreparedStatement ps = null;

        // 5.岗位信息
        query = getConfig().getQuery("App-PersonPosition");
        if (query != null) {
            sqlClause = query.getSqlClause("basic");
            if (sqlClause != null) {
                columnMap = sqlClause.getColumnMappings();
                strSql = buildSql(sqlClause, app.getID());
                app.setRoleIdList_is_initialized(true);
                app.setRecursiveChildIdList(null);

                try {
                    c = this.getConnection();
                    ps = c.prepareStatement(strSql.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                    if (ps.execute()) {
                        rs = ps.executeQuery();
                        while (rs.next()) {
                            app.addRole(getString(rs, columnMap.get("personPositionId")));
                        }
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                } finally {
                    getManager().close(ps, rs);
                    freeConnection(c);
                }

            }
        }
    }

    synchronized void loadGroups(DbApp app) {

        ResultSet rs = null;
        Query query = null;
        SqlClause sqlClause = null;
        Map columnMap = null;
        String strSql = null;
        Connection c = null;
        PreparedStatement ps = null;

        // 6.工作组信息
        query = getConfig().getQuery("App-PersonGroup");
        if (query != null) {
            sqlClause = query.getSqlClause("basic");
            if (sqlClause != null) {
                columnMap = sqlClause.getColumnMappings();
                strSql = buildSql(sqlClause, app.getID());
                app.setRoleIdList_is_initialized(true);
                app.setRecursiveChildIdList(null);

                try {
                    c = this.getConnection();
                    ps = c.prepareStatement(strSql.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                    if (ps.execute()) {
                        rs = ps.executeQuery();
                        while (rs.next()) {
                            app.addRole(getString(rs, columnMap.get("personGroupId")));
                        }
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                } finally {
                    getManager().close(ps, rs);
                    freeConnection(c);
                }

            }
        }
    }

    private String buildGetChildrenSql(SqlClause sqlClause, String ID) {
        Map columnMap = sqlClause.getColumnMappings();
        String strSql = sqlClause.getMainClause();
        String strWhere = sqlClause.getWhereClause();
        if (strWhere != null && !strWhere.equals("")) {
            strSql = strSql + " " + strWhere + " AND " + ((ColumnMapping) columnMap.get("parentAppId")).getColumn() + "='" + ID + "'";
        } else {
            strSql = strSql + " WHERE " + ((ColumnMapping) columnMap.get("parentAppId")).getColumn() + "='" + ID + "'";
        }
        return strSql;
    }

    private synchronized DbApp loadFromDb(String ID) throws AppNotFoundException {
        DbApp app = loadBasic(ID);
        loadModules(app);
        loadChildren(app);
        return app;
    }

    /**
     * 取得该应用下的所有模块
     */
    void loadModules(DbApp app) {

        ResultSet rs = null;
        Query query = null;
        SqlClause sqlClause = null;
        Map columnMap = null;
        String strSql = null;
        Connection c = null;
        PreparedStatement ps = null;
        // 5.子应用信息
        query = getConfig().getQuery("Module-App");
        if (query != null) {
            sqlClause = query.getSqlClause("basic");
            if (sqlClause != null) {
                columnMap = sqlClause.getColumnMappings();
                strSql = buildGetChildrenSql(sqlClause, app.getID());

                app.setModuleIdList_is_initialized(true);
                app.setModuleIdList(null);

                try {
                    c = this.getConnection();
                    ps = c.prepareStatement(strSql.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                    if (ps.execute()) {
                        rs = ps.executeQuery();
                        while (rs.next()) {
                            app.addModule(getString(rs, columnMap.get("moduleId")));
                        }
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                } finally {
                    getManager().close(ps, rs);
                    freeConnection(c);
                }
            }
        }
    }

    private String buildSql(SqlClause sqlClause, String ID) {
        Map columnMap = sqlClause.getColumnMappings();
        String strSql = sqlClause.getMainClause();
        String strWhere = sqlClause.getWhereClause();
        if (strWhere != null && !strWhere.equals("")) {
            strSql = strSql + " " + strWhere + " AND " + ((ColumnMapping) columnMap.get("appId")).getColumn() + "='" + ID + "'";
        } else {
            strSql = strSql + " WHERE " + ((ColumnMapping) columnMap.get("appId")).getColumn() + "='" + ID + "'";
        }
        return strSql;
    }

    public void insert(DbApp app) {

        Connection c = null;
        PreparedStatement ps = null;
        Query query = getConfig().getQuery("App");
        SqlClause sqlClause = query.getSqlClause("basic");
        Map columnMap = sqlClause.getColumnMappings();
        StringBuffer _sql = new StringBuffer(sqlClause.getInsertClause() + "(");

        try {

            c = this.getConnection();
            int _dirtyCount = 0;
            _sql.append(columnMap.get("appId")).append(",");
            _dirtyCount++;
            _sql.append(columnMap.get("appName")).append(",");
            _dirtyCount++;
            _sql.append(columnMap.get("enName")).append(",");
            _dirtyCount++;
            _sql.append(columnMap.get("icon")).append(",");
            _dirtyCount++;
            _sql.append(columnMap.get("index")).append(",");
            _dirtyCount++;
            _sql.append(columnMap.get("parentAppId")).append(",");
            _dirtyCount++;
            _sql.append(columnMap.get("sysid")).append(",");
            _dirtyCount++;
            _sql.setLength(_sql.length() - 1);
            _sql.append(") values (");
            for (int i = 0; i < _dirtyCount; i++)
                _sql.append("?,");
            _sql.setLength(_sql.length() - 1);
            _sql.append(")");

            ps = c.prepareStatement(_sql.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            _dirtyCount = 0;
            ps.setString(++_dirtyCount, app.getID());
            ps.setString(++_dirtyCount, app.getName());
            ps.setString(++_dirtyCount, app.getEnName());
            ps.setString(++_dirtyCount, app.getIcon());
            ps.setInt(++_dirtyCount, app.getIndex());
            ps.setString(++_dirtyCount, app.getParentId());
            // ps.setString(++_dirtyCount, this.getBrief());
            ps.setString(++_dirtyCount, app.getSubSystemId().getType());
            ps.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            getManager().close(ps);
            freeConnection(c);
        }

    }

}
